package com.mcl.mydinglib;


import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;

import jxl.Sheet;
import jxl.Workbook;


public class ExcelTest {

    private static StringBuffer stringBuffer = new StringBuffer();

    public static void main(String[] args) {
        try {
            //readExcel(new File("/Users/apple/Documents/t.xlsx"));
            readPoiExcel();
        } catch (Exception e) {
            System.out.println(e);
        }
    }

    public static void readPoiExcel() {
        try {
            XSSFWorkbook workbook = new XSSFWorkbook("/Users/apple/Documents/t.xlsx");
            XSSFSheet sheet = workbook.getSheetAt(0);
            int rowsCount = sheet.getPhysicalNumberOfRows();
            FormulaEvaluator formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator();
            for (int r = 0; r < rowsCount; r++) {
                Row row = sheet.getRow(r);
                int cellsCount = row.getPhysicalNumberOfCells();
                for (int c = 0; c < cellsCount; c++) {
                    String value = getCellAsString(row, c, formulaEvaluator);
                    String cellInfo = "r:" + r + "; c:" + c + "; v:" + value;

                    stringBuffer.append(cellInfo + "\n");
                }
            }
            printlnToUser();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    protected static String getCellAsString(Row row, int c, FormulaEvaluator formulaEvaluator) {
        String value = "";
        try {
            Cell cell = row.getCell(c);
            CellValue cellValue = formulaEvaluator.evaluate(cell);
            switch (cellValue.getCellType()) {
                case Cell.CELL_TYPE_BOOLEAN:
                    value = "" + cellValue.getBooleanValue();
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    double numericValue = cellValue.getNumberValue();
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        double date = cellValue.getNumberValue();
                        SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yy");
                        value = formatter.format(HSSFDateUtil.getJavaDate(date));
                    } else {
                        value = "" + numericValue;
                    }
                    break;
                case Cell.CELL_TYPE_STRING:
                    value = "" + cellValue.getStringValue();
                    break;
                default:
            }
        } catch (NullPointerException e) {
            stringBuffer.append("null\n");
            printlnToUser();
        }
        return value;
    }

    /**
     * print line to the output TextView
     *
     */
    private static void printlnToUser() {
        System.out.println(stringBuffer.toString());
    }

    public static void readExcel(File file) {
        try {
            InputStream is = new FileInputStream(file);
            Workbook book = Workbook.getWorkbook(is);
            book.getNumberOfSheets();
            Sheet sheet = book.getSheet(0);
            int Rows = sheet.getRows();

            for (int i = 1; i < Rows; ++i) {
                String name = (sheet.getCell(0, i)).getContents();
                String department = (sheet.getCell(1, i)).getContents();
                String company = (sheet.getCell(2, i)).getContents();
                String phone = (sheet.getCell(3, i)).getContents();

                System.out.println("yy" + "第" + i + "行数据=" + name + "," + department + "," + company + "," + phone);

            }
            book.close();

        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
    }


}
